from sqlalchemy import select, update, delete
from sqlalchemy.ext.asyncio import AsyncSession

from module_kylin.entity.do.strategy_tradebook_do import KylinStrategyTradebook
from module_kylin.entity.vo.strategy_tradebook_vo import (
    StrategyTradebookModel,
    StrategyTradebookPageQueryModel,
)
from utils.page_util import PageUtil


class StrategyTradebookDao:
    """
    策略交易登记簿数据库操作层
    """

    @classmethod
    async def get_strategy_tradebook_by_id(cls, db: AsyncSession, tradebook_id: int):
        """
        根据ID获取策略交易登记簿信息

        :param db: orm对象
        :param tradebook_id: 登记簿id
        :return: 策略交易登记簿信息对象
        """
        tradebook_info = (
            await db.execute(select(KylinStrategyTradebook).where(KylinStrategyTradebook.id == tradebook_id))
        ).scalars().first()

        return tradebook_info

    @classmethod
    async def get_strategy_tradebook_by_info(cls, db: AsyncSession, tradebook: StrategyTradebookModel):
        """
        根据策略实例名称和价格获取交易登记簿信息

        :param db: orm对象
        :param tradebook: 策略交易登记簿对象
        :return: 策略交易登记簿信息对象
        """
        query = select(KylinStrategyTradebook)
        
        if tradebook.name:
            query = query.where(KylinStrategyTradebook.name == tradebook.name)
        
        if tradebook.price is not None:
            query = query.where(KylinStrategyTradebook.price == tradebook.price)
        
        tradebook_info = (await db.execute(query)).scalars().first()
        return tradebook_info

    @classmethod
    async def get_strategy_tradebook_list(cls, db: AsyncSession, query_model: StrategyTradebookPageQueryModel, is_page: bool = False):
        """
        获取策略交易登记簿列表

        :param db: orm对象
        :param query_model: 查询参数对象
        :param is_page: 是否开启分页
        :return: 策略交易登记簿列表
        """
        query = select(KylinStrategyTradebook)
        
        if query_model.name:
            # 使用ilike进行不区分大小写的模糊匹配
            query = query.where(KylinStrategyTradebook.name.ilike(f'%{query_model.name}%'))
        
        # 按策略实例名称升序，价格降序排序
        query = query.order_by(KylinStrategyTradebook.name.asc(), KylinStrategyTradebook.price.desc())
        
        # 构建分页查询
        strategy_tradebook_page = await PageUtil.paginate(db, query, query_model.page_num, query_model.page_size, is_page)
        
        return strategy_tradebook_page

    @classmethod
    async def add_strategy_tradebook(cls, db: AsyncSession, tradebook: StrategyTradebookModel):
        """
        添加策略交易登记簿

        :param db: orm对象
        :param tradebook: 策略交易登记簿对象
        :return: 操作结果
        """
        db_tradebook = KylinStrategyTradebook(
            name=tradebook.name,
            price=tradebook.price,
            buy=tradebook.buy,
            sell=tradebook.sell,
            bought=tradebook.bought,
            sold=tradebook.sold
        )
        db.add(db_tradebook)
        await db.flush()
        return db_tradebook

    @classmethod
    async def update_strategy_tradebook(cls, db: AsyncSession, tradebook_id: int, tradebook: StrategyTradebookModel):
        """
        更新策略交易登记簿

        :param db: orm对象
        :param tradebook_id: 登记簿id
        :param tradebook: 策略交易登记簿对象
        :return: 操作结果
        """
        update_data = {}
        if tradebook.name is not None:
            update_data[KylinStrategyTradebook.name] = tradebook.name
        if tradebook.price is not None:
            update_data[KylinStrategyTradebook.price] = tradebook.price
        if tradebook.buy is not None:
            update_data[KylinStrategyTradebook.buy] = tradebook.buy
        if tradebook.sell is not None:
            update_data[KylinStrategyTradebook.sell] = tradebook.sell
        if tradebook.bought is not None:
            update_data[KylinStrategyTradebook.bought] = tradebook.bought
        if tradebook.sold is not None:
            update_data[KylinStrategyTradebook.sold] = tradebook.sold
        
        await db.execute(
            update(KylinStrategyTradebook)
            .where(KylinStrategyTradebook.id == tradebook_id)
            .values(update_data)
        )

    @classmethod
    async def delete_strategy_tradebook(cls, db: AsyncSession, tradebook_id: int):
        """
        删除策略交易登记簿

        :param db: orm对象
        :param tradebook_id: 登记簿id
        :return: 操作结果
        """
        await db.execute(
            delete(KylinStrategyTradebook)
            .where(KylinStrategyTradebook.id == tradebook_id)
        )

    @classmethod
    async def batch_delete_strategy_tradebook(cls, db: AsyncSession, tradebook_ids: list[int]):
        """
        批量删除策略交易登记簿

        :param db: orm对象
        :param tradebook_ids: 登记簿id列表
        :return: 操作结果
        """
        await db.execute(
            delete(KylinStrategyTradebook)
            .where(KylinStrategyTradebook.id.in_(tradebook_ids))
        )
    
    @classmethod
    async def truncate_strategy_tradebook(cls, db: AsyncSession):
        """
        清空策略交易登记簿表
        
        :param db: orm对象
        :return: 操作结果
        """
        await db.execute(
            delete(KylinStrategyTradebook)
        )